﻿Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.OleDb
Imports System.Windows.Forms
Imports System.Xml


Public Class Main

    Public Property runName As String
    Public Property machineName As String
    Public Property instanceName As String


    Private PerflogCT As PerfLogContext
    Dim Counters As XDocument



    Private Sub PLE_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load


        Counters = XDocument.Load(".\CounterThresholds\Counters.xml")


        PerflogCT = New PerfLogContext

        FillSelectRunCombo()

        FillSelectPerformanceObject()


    End Sub

#Region "  Run Selection Code  "

    Private Sub FillSelectRunCombo()

        'Fill Combobox with collections


        Try

            Dim QueryRuns = From rows In PerflogCT.DisplayToIDs
                            Order By rows.DisplayString
                            Select rows.DisplayString, rows.LogStartTime, rows.LogStopTime, Title = rows.DisplayString + ": " + rows.LogStartTime.Trim + " - " + rows.LogStopTime.Trim


            With Me.cmbDisplayIds
                .DataSource = QueryRuns.AsEnumerable
                .ValueMember = "DisplayString"
                .DisplayMember = "Title"
            End With

            Me.cmbMachineNames.Enabled = True
            Me.cmbInstanceNames.Enabled = True

        Catch ex As Exception

        End Try
    End Sub

    Private Sub cmbDisplayIds_SelectedValueChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles cmbDisplayIds.SelectedValueChanged
        'Dim PerflogCT As New PerfLogContext

        Try
            Me.runName = CStr(cmbDisplayIds.SelectedValue)
            'Fill MachineNames
            Dim QueryMachineNames = (From rows In PerflogCT.DisplayToIDs
                                     Where rows.DisplayString.Equals(Me.cmbDisplayIds.SelectedValue)
                                    Select rows.CounterDatas.CounterDetail.MachineName).Distinct

            With Me.cmbMachineNames
                .DataSource = QueryMachineNames.AsEnumerable
            End With

            'Fill InstanceNames
            'Default instance start with SQLServer:
            'Named instancenames start with MSSQL$
            Dim QueryInstanceNames = (From rows In PerflogCT.CounterDetails _
                                     Where (rows.ObjectName.Contains("SQLServer:") Or rows.ObjectName.Contains("MSSQL$")) _
                                     And rows.CounterDatas.CounterDetail.MachineName.Equals(cmbMachineNames.Text) _
                                     Select InstanceName = IIf(rows.ObjectName.Contains("SQLServer:"), "Default", rows.ObjectName.Substring(6, rows.ObjectName.IndexOf(":") - 6))).Distinct

            With Me.cmbInstanceNames
                .DataSource = QueryInstanceNames.AsEnumerable
            End With

        Catch ex As Exception

        End Try

    End Sub


    Private Sub cmbInstanceNames_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles cmbInstanceNames.SelectedIndexChanged
        Me.instanceName = cmbInstanceNames.Text
    End Sub

    Private Sub cmbMachineNames_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles cmbMachineNames.SelectedIndexChanged
        Me.machineName = cmbMachineNames.Text
    End Sub

    Private Sub cmdRun_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles cmdRun.Click
        Cursor = Cursors.WaitCursor

        ClearGroupBoxes(Me.gbBufferManager)
        ClearGroupBoxes(Me.gbOSMemory)
        ClearGroupBoxes(Me.gbSQLWorkload)
        ClearGroupBoxes(Me.gbSQLServerMemManager)

        MemoryData()
        OSMemory_Paging()
        CPUData()
        SQLWorkloadPerformance()
        SQLMemoryManager()
        DiskIO()
        Cursor = Cursors.Default
    End Sub

    Private Sub ClearGroupBoxes(ByVal gb As GroupBox)
        For Each ctl In gb.Controls
            If TypeOf ctl Is TextBox Then
                CType(ctl, TextBox).Text = String.Empty
                CType(ctl, TextBox).BackColor = Color.FromKnownColor(KnownColor.Control)
            End If
        Next
    End Sub
#End Region

#Region "   Graph and Dashboard Code   "

    

    Private Sub CPUData()
        Try
            Dim QueryCPU = From rows In PerflogCT.CounterDatas _
                           Where rows.CounterDetail.MachineName.Equals(machineName) _
                           And rows.CounterDetail.ObjectName.Equals("Processor") _
                           And rows.CounterDetail.CounterName.Equals("% Processor Time") _
                           And Not rows.CounterDetail.InstanceName.Equals("_Total") _
                           And rows.DisplayToID.DisplayString.Equals(runName) _
                           Group rows By rows.CounterDetail.InstanceName _
                           Into lowValue = Min(rows.CounterValue), maxValue = Max(rows.CounterValue), avgValue = Average(rows.CounterValue) _
                        Select Instance = InstanceName, min = lowValue, max = maxValue, avg = avgValue


            With Me.dgCPU
                .DataSource = QueryCPU.AsEnumerable
                .Columns(0).Width = 60
                .Columns(0).HeaderText = "CPU"
                .Columns(1).Width = 60
                .Columns(1).HeaderText = "Min.(%)"
                .Columns(2).Width = 60
                .Columns(2).HeaderText = "Max.(%)"
                .Columns(3).Width = 60
                .Columns(3).HeaderText = "Avg.(%)"
            End With

            Dim QueryCPUGraph = From rows In PerflogCT.CounterDatas _
                                    Where rows.CounterDetail.CounterName.Equals("% Processor Time") _
                                    And rows.CounterDetail.ObjectName.Equals("Processor") _
                                    And Not rows.CounterDetail.InstanceName.Equals("_Total") _
                                    And rows.CounterDetail.MachineName.Equals(machineName) _
                                    And rows.DisplayToID.DisplayString.Equals(runName) _
                                    Select rows.CounterDetail.InstanceName, rows.CounterValue, rows.CounterDateTime


            Dim CPUs = (From Cpu In QueryCPUGraph
                       Select Cpu.InstanceName).Distinct

            Me.SimpleCPUGraph.Series.Clear()
            For Each CPU In CPUs
                With Me.SimpleCPUGraph
                    .Series.Add("CPU-" + CPU.ToString)
                    With .Series("CPU-" + CPU.ToString)
                        .Points.DataBind((From rows In QueryCPUGraph
                                                                    Where rows.InstanceName.Equals(CPU)
                                                                    Select rows.CounterValue, rows.CounterDateTime).AsEnumerable, "CounterDateTime", "CounterValue", String.Empty)
                        .ChartType = DataVisualization.Charting.SeriesChartType.Line
                    End With
                    With .ChartAreas(0)
                        .AxisY.LabelStyle.IsEndLabelVisible = True
                        .AxisY.IntervalAutoMode = DataVisualization.Charting.IntervalAutoMode.FixedCount

                    End With


                End With
            Next

            Me.SimpleCPUGraph.Invalidate()
        Catch ex As Exception

        End Try
    End Sub

    Private Sub dgCPU_CellFormatting(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellFormattingEventArgs) Handles dgCPU.CellFormatting

        If dgCPU.Columns(e.ColumnIndex).HeaderText.Equals("Min.(%)") Then
            If CInt(e.Value) > 80 Then
                e.CellStyle.BackColor = Color.Red
                e.CellStyle.SelectionBackColor = Color.DarkRed
            ElseIf CInt(e.Value) > 60 Then
                e.CellStyle.BackColor = Color.Orange
                e.CellStyle.SelectionBackColor = Color.SandyBrown
            Else
                e.CellStyle.BackColor = Color.LawnGreen
                e.CellStyle.SelectionBackColor = Color.Olive
            End If
        End If

        If dgCPU.Columns(e.ColumnIndex).HeaderText.Equals("Max.(%)") Then
            If CInt(e.Value) > 80 Then
                e.CellStyle.BackColor = Color.Red
                e.CellStyle.SelectionBackColor = Color.DarkRed
            ElseIf CInt(e.Value) > 60 Then
                e.CellStyle.BackColor = Color.Orange
                e.CellStyle.SelectionBackColor = Color.SandyBrown
            Else
                e.CellStyle.BackColor = Color.LawnGreen
                e.CellStyle.SelectionBackColor = Color.Olive
            End If
        End If

        If dgCPU.Columns(e.ColumnIndex).HeaderText.Equals("Avg.(%)") Then
            If CInt(e.Value) > 80 Then
                e.CellStyle.BackColor = Color.Red
                e.CellStyle.SelectionBackColor = Color.DarkRed
            ElseIf CInt(e.Value) > 60 Then
                e.CellStyle.BackColor = Color.Orange
                e.CellStyle.SelectionBackColor = Color.SandyBrown
            Else
                e.CellStyle.BackColor = Color.LawnGreen
                e.CellStyle.SelectionBackColor = Color.Olive
            End If
        End If


    End Sub

#End Region


    Private Sub SparkLinePageLifeExpectancy_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles SparkLinePageLifeExpectancy.Click
        Dim frmGraph As New GraphDetail("Page life expectancy", CStr(IIf(instanceName = "Default", "SQLServer:Buffer Manager", "MSSQL$" + instanceName + ":Buffer Manager")))

        frmGraph.ShowDialog(Me)


    End Sub
End Class
